package cn.saltybe.analysis.service;

import cn.saltybe.analysis.entity.result.RentingHouseAveragePrice;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class RentingHouseAveragePriceStatisticServiceImpl implements RentingHouseAveragePriceStatisticService {
    private JdbcTemplate jdbcTemplate;

    // set方法注入JdbcTemplate
    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) throws Exception {
        this.jdbcTemplate = jdbcTemplate;
        this.createTable("whole");
        this.createTable("share");
    }

    private final BeanPropertyRowMapper<RentingHouseAveragePrice> rowMapper = new BeanPropertyRowMapper<>(RentingHouseAveragePrice.class);

    private String toTableName(String type) {
        type = type.toLowerCase().equals("share") ? "share" : "whole";
        return type + "_renting_house_average_price";
    }

    public void createTable(String type) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        type = type.toLowerCase().equals("share") ? "share" : "whole";
        final String sql = "CREATE TABLE IF NOT EXISTS `" + toTableName(type) + "` (" +
                "`statistic_time` DATETIME NOT NULL," +
                "`city` VARCHAR(8) NOT NULL," +
                "`district` VARCHAR(8) NOT NULL," +
                "`average_price` DOUBLE NOT NULL" +
                ") ENGINE = InnoDB CHARSET = utf8mb4";
        this.jdbcTemplate.execute(sql);
    }

    public void dropTable(String type) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        type = type.toLowerCase().equals("share") ? "share" : "whole";
        final String sql = "DROP TABLE `" + toTableName(type) + "`";
        this.jdbcTemplate.execute(sql);
    }

    @Override
    public void insert(String type, RentingHouseAveragePrice rentingHouseAveragePrice) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "INSERT INTO `" + toTableName(type) + "` (`statistic_time`, `city`, `district`, `average_price`) VALUES (?, ?, ?, ?)";
        Object[] params = new Object[] { rentingHouseAveragePrice.getStatistic_time(), rentingHouseAveragePrice.getCity(), rentingHouseAveragePrice.getDistrict(), rentingHouseAveragePrice.getAverage_price() };
        jdbcTemplate.update(sql, params);
    }

    @Override
    public List<RentingHouseAveragePrice> all(String type) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "SELECT * FROM `" + toTableName(type) + "`";
        List<RentingHouseAveragePrice> list = this.jdbcTemplate.query(sql, rowMapper);
        return list;
    }

    @Override
    public List<RentingHouseAveragePrice> query(String type, String city) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "SELECT * FROM `" + toTableName(type) + "` WHERE `city` = ?";
        Object[] params = new Object[] { city };
        List<RentingHouseAveragePrice> list = this.jdbcTemplate.query(sql, rowMapper, params);
        return list;
    }

    @Override
    public List<RentingHouseAveragePrice> query(String type, String[] city) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        if (city.length == 0) return new ArrayList<RentingHouseAveragePrice>();
        String conditions = "";
        Object[] params = new Object[city.length];
        for (int i = 0; i < city.length; i++) {
            conditions += "`city` = ?";
            params[i] = city[i];
            if (i + 1 != city.length) conditions += " OR ";
        }
        final String sql = "SELECT * FROM `" + toTableName(type) + "` WHERE " + conditions;
        List<RentingHouseAveragePrice> list = this.jdbcTemplate.query(sql, rowMapper, params);
        return list;
    }

    @Override
    public int delete(String type, String city, String district) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "DELETE FROM `" + toTableName(type) + "` WHERE `city` = ? AND `district` = ?";
        Object[] params = new Object[] { city, district };
        return this.jdbcTemplate.update(sql, params);
    }
}
